package com.ld.hpfs.common.utils;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class WbExcel {
	public static void writerExcel(List<HashMap<String,Object>> list,String path){

		HSSFWorkbook workbook= new HSSFWorkbook();
		DecimalFormat fmt=new DecimalFormat("0.##");
		HSSFSheet sheet = workbook.createSheet();
	       // 创建数据表头
        String titles[] = { "销售单据号","发票号码","发票代码","发票日期","购方名称","不含税金额","税额","票据状态","票据类别代码" };

        HSSFRow row = sheet.createRow(0);
        HSSFCellStyle items_style = workbook.createCellStyle();
        items_style.setAlignment((short) HSSFCellStyle.ALIGN_CENTER);

        HSSFFont celltbnamefont = workbook.createFont();
        celltbnamefont.setFontHeightInPoints((short) 10);

        celltbnamefont.setColor((short) (HSSFFont.COLOR_RED));
        items_style.setFont(celltbnamefont);
        items_style.setWrapText(true);
		
        for (int i = 0; i < titles.length; i++) {
            HSSFCell cell = row.createCell(i);
            sheet.setColumnWidth(i, 5335);
            cell.setCellValue(titles[i]);
            cell.setCellStyle(items_style);
        }

        HSSFCellStyle datestyle = workbook.createCellStyle();
        HSSFDataFormat df = workbook.createDataFormat();
        datestyle.setDataFormat(df.getFormat("yyyy-mm-dd"));

        int rowNum = 1;
        for (HashMap<String,Object> entry : list) {
            // create the row data  
           entry.remove("ID_HEAD_ID");
           entry.remove("COMPANY_ID");
           HSSFRow datarow = sheet.createRow(rowNum++);          
           Collection collection= entry.values();
           Iterator its = collection.iterator();
    
           		String Status = entry.get("doc_status").toString();
        	   datarow.createCell(0).setCellValue(entry.get("document_no").toString() == null ? "" : entry.get("document_no").toString());
               datarow.createCell(1).setCellValue(entry.get("invoice_no") == null ? "" : entry.get("invoice_no").toString());
               datarow.createCell(2).setCellValue(entry.get("invoice_code") == null ? "" : entry.get("invoice_code").toString());
               datarow.createCell(3).setCellValue(entry.get("invoice_date") == null ? "" : entry.get("invoice_date").toString());
               datarow.createCell(4).setCellValue(entry.get("cust_name_chn") == null ? "" : entry.get("cust_name_chn").toString());
               datarow.createCell(5).setCellValue(entry.get("amount_cur_total") == null ? "" : fmt.format(entry.get("amount_cur_total")));
               datarow.createCell(6).setCellValue(entry.get("tax_amount") == null ? "" : fmt.format(entry.get("tax_amount")));
               datarow.createCell(7).setCellValue(Status.equals("1") ? "已开票" :  Status.equals("5") ? "已红冲" :  Status.equals("9") ? "已回写" : "票据状态未知");
               datarow.createCell(8).setCellValue(entry.get("doc_type_code") == null ? "" : entry.get("doc_type_code").toString());

        }

		
        File file = new File(path);
		try {			
				file.createNewFile();
				FileOutputStream out = new FileOutputStream(file);
				workbook.write(out);				
		} catch (IOException e) {
			
			e.printStackTrace();
		}

		
	}
	public static  String FormateDownloadData(ArrayList<HashMap<String,Object>> revenueData,int nums){//拼写txt文件内容
		try{
			Date date = new Date();
			SimpleDateFormat from = new SimpleDateFormat("yyyyMMdd"); //获取服务器时间并格式化，格式为"yyyy-MM-dd"
			String SYSTIME = from.format(date);   //SYSTEM TIME
			String num = nums+"";//发票打印张数
			StringBuffer sb = new StringBuffer();
		    sb.append("SJJK0201~~销售单据传出");
		    sb.append("\r\n");
		    sb.append(num).append("~~").append(SYSTIME).append("~~").append(SYSTIME);
		    sb.append("\r\n");
		    if (!revenueData.isEmpty())
		    {
//		    	sb.append( "DOCUMENT_NO~~").append("COUNT~~").append("CUST_NAME_CHN~~").append("TAX_CODE~~").append("ADDRESS~~").append("BANK_ACCOUNT~~")
//	            .append("REMARK~~").append("CHECKER~~").append("REMITTEE~~")
//	            .append("GOODS_LIST_NAME~~").append("DOCUMENT_DATE~~").append("SELLER_BANK_ACCOUNT~~")
//	            .append("SELLER_ADDRESS~~");
//	            sb.append("\r\n");	
		    	
		        for (int i = 0; i < revenueData.size(); i++)
		        {
		        	ArrayList<HashMap<String,Object>> details_other =(ArrayList<HashMap<String,Object>>)revenueData.get(i).get("details");
		        	String tax_rate_main = "";
		        	try{
		        		tax_rate_main = details_other.get(0).get("TAX_RATE") == null ? "" : details_other.get(0).get("TAX_RATE").toString();
		        	}catch(Exception ex33){
		        		tax_rate_main = "";
		        	}
		            String  document_no = revenueData.get(i).get("DOCUMENT_NO").toString();
//		            String  doc_type_code = revenueData.get(i).get("DOC_TYPE_CODE").toString();
		            String cust_name_chn = revenueData.get(i).get("CUST_NAME_CHN").toString();
		            String total_line_no = revenueData.get(i).get("TOTAL_LINE_NO").toString();
		            String tax_code = revenueData.get(i).get("TAX_CODE").toString();
		            String doc_type_code = revenueData.get(i).get("DOC_TYPE_CODE") == null ? "" : revenueData.get(i).get("DOC_TYPE_CODE").toString();
//		            String doc_type = revenueData.get(i).get("DOCUMENT_TYPE") == null ? "" : revenueData.get(i).get("DOCUMENT_TYPE").toString();
		            String address = revenueData.get(i).get("ADDRESS").toString();
		            String bank_account = revenueData.get(i).get("BANK_ACCOUNT").toString();
		            String untax_money = revenueData.get(i).get("AMOUNT_CUR_TOTAL") == null ? "" : revenueData.get(i).get("AMOUNT_CUR_TOTAL").toString();
		            String void_flag = revenueData.get(i).get("VOID_FLAG") == null ? "" : revenueData.get(i).get("VOID_FLAG").toString();
		            String main_tax_amount = revenueData.get(i).get("TAX_AMOUNT") == null ? "" : revenueData.get(i).get("TAX_AMOUNT").toString();
		            String remark = revenueData.get(i).get("REMARK") == null ? "" : revenueData.get(i).get("REMARK").toString();
		            String checker = revenueData.get(i).get("CHECKER").toString();
		            String remittee = revenueData.get(i).get("REMITTEE").toString();
//		            String goods_list_name = revenueData.get(i).get("GOODS_LIST_NAME").toString();
		            String document_date = revenueData.get(i).get("DOCUMENT_DATE").toString();
		            String seller_bank_account = revenueData.get(i).get("SELLER_BANK_ACCOUNT").toString();
		            String invoice_no = revenueData.get(i).get("INVOICE_NO") == null ? "" : revenueData.get(i).get("INVOICE_NO").toString();
		            String month = document_date.length()<1 ? "" : document_date.substring(4,6);
		            String seller_tax_code = revenueData.get(i).get("SELLER_TAX_CODE").toString();
		            String list_mark = Integer.parseInt(total_line_no) > 8 ? "1" : "0";
		            String seller_name = revenueData.get(i).get("SELLER_NAME").toString();
		            String seller_address = revenueData.get(i).get("SELLER_ADDRESS").toString();
		            String operator = revenueData.get(i).get("OPERATOR").toString();
		            String count = revenueData.get(i).get("count").toString();
		            sb.append(void_flag).append("~~").append(list_mark).append("~~")/*.append(doc_type).append("~~")*/.append(doc_type_code.equals("VAT") ? "0" : "1").append("~~").append(invoice_no).append("~~")
		              .append(total_line_no==null?"":total_line_no).append("~~").append(document_date==""?"":document_date).append("~~").append(month).append("~~")
		              .append(document_no==null?"":document_no).append("~~").append(untax_money==null?"":untax_money).append("~~").append(tax_rate_main).append("~~").append(main_tax_amount==""?"~~":main_tax_amount).append("~~")
		              .append(cust_name_chn==null?"":cust_name_chn).append("~~").append(tax_code==null?"":tax_code).append("~~")
		              .append(address==null?"":address).append("~~").append(bank_account==null?"":bank_account).append("~~")
		              .append(seller_name==null?"":seller_name).append("~~").append(seller_tax_code==null?"":seller_tax_code).append("~~")
		              .append(seller_address==null?"":seller_address).append("~~").append(seller_bank_account==null?"":seller_bank_account).append("~~")
		              .append(remark).append("~~").append(operator==null?"":operator).append("~~")
		              .append(checker==null?"":checker).append("~~").append(remittee==null?"":remittee);
		            sb.append("\r\n");
		            if(Integer.parseInt(count)>0){
		            	ArrayList<HashMap<String,Object>> details =(ArrayList<HashMap<String,Object>>)revenueData.get(i).get("details");
		            	for(int j = 0; j < details.size(); j++){
		            	   String goods_name = details.get(j).get("GOODS_NAME").toString();
		            	   String goods_unit = details.get(j).get("GOODS_UNIT").toString();
		            	   String spec = details.get(j).get("SPEC").toString();
		            	   String discount_line = "0";
		            	   String goods_quantityname_chn = details.get(j).get("QUANTITY") == null ? "" : details.get(j).get("QUANTITY").toString();
		            	   String amount_cur = details.get(j).get("AMOUNT_CUR") == null ? "" : details.get(j).get("AMOUNT_CUR").toString();
		            	   String tax_rate = details.get(j).get("TAX_RATE") == null ? "" : details.get(j).get("TAX_RATE").toString();
		            	   String tax_item = details.get(j).get("TAX_ITEM").toString();
//		            	   String discount_sum = details.get(j).get("DISCOUNT_SUM").toString();
		            	   String tax_amount = details.get(j).get("TAX_AMOUNT") == null ? "" : details.get(j).get("TAX_AMOUNT").toString();
		            	 
//		            	   String discount_tax = details.get(j).get("DISCOUNT_TAX").toString();
//		            	   String discount_rate = details.get(j).get("DISCOUNT_RATE").toString();
		            	   String price_cur = details.get(j).get("PRICE_CUR") == null ? "" : details.get(j).get("PRICE_CUR").toString();
		            	   String price_way = details.get(j).get("PRICE_WAY").toString();
		            	   sb.append(discount_line).append(goods_name==null?"":goods_name).append("~~").append(spec==null?"":spec).append("~~")
		            	   	  .append(goods_unit==null?"":goods_unit).append("~~")
		            	   	  .append(goods_quantityname_chn).append("~~").append(amount_cur).append("~~").append(tax_rate==null?"":tax_rate).append("~~")
				              .append(tax_amount).append("~~").append(price_cur==null?"":price_cur).append("~~")
				              .append(price_way).append("~~").append(tax_item==null?"":tax_item);
				           sb.append("\r\n");
				           
		            	}
		            }
		        }
		    }		
		    try
		    {
		        return sb.toString();
		    }
		    catch (Exception e)
		    {
		        e.printStackTrace();
		    }
		}catch(Exception e2){
			e2.printStackTrace();
		}
		
	
	    return "";
	}
}
